import paramiko
import psycopg2
import datetime

# get province code
def get_province_code(province):
    con = psycopg2.connect(
        database="sentinel2",
        user="postgres",
        password="12345",
        host="192.168.2.172",
        port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT qhdm
    FROM china_sy
    WHERE qhmc = '{province}' 
    """
    cur.execute(sql)
    con.commit()
    code=[record[0] for record in cur.fetchall()][0]
    cur.close()
    con.close()
    return code

# get sentinel2 tile list
def get_province_tiles(province):
	con = psycopg2.connect(
	database="sentinel2",
	user="postgres",
	password="12345",
	host="192.168.2.172",
	port="5432"
	)
	cur = con.cursor()
	sql = f"""
	SELECT "中国有效哨兵格网分布wgs84".mgrs
	FROM china_sy
	JOIN "中国有效哨兵格网分布wgs84" 
	on st_intersects(china_sy.geom,"中国有效哨兵格网分布wgs84".geom)
	WHERE  china_sy.qhmc = '{province}' 
	"""
	cur.execute(sql)
	con.commit()
	tiles=[record[0] for record in cur.fetchall()]
	tiles = [tile.upper() for tile in tiles]
	cur.close()
	con.close()
	return tiles

# get sentinel2 tile list
def get_city_tiles(province, city):
    con = psycopg2.connect(
    database="sentinel2",
    user="postgres",
    password="12345",
    host="192.168.2.172",
    port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT "中国有效哨兵格网分布wgs84".mgrs
    FROM china_dsy
    JOIN "中国有效哨兵格网分布wgs84" 
    on st_intersects(china_dsy.geom,"中国有效哨兵格网分布wgs84".geom)
    WHERE  china_dsy.qhmc = '{city}' 
    AND sjxzqdm in (
        SELECT qhdm
        FROM china_sy
        WHERE  china_sy.qhmc = '{province}' 
    )
    """
    cur.execute(sql)
    con.commit()
    tiles=[record[0] for record in cur.fetchall()]
    tiles = [tile.upper() for tile in tiles]
    cur.close()
    con.close()
    return tiles

# get sentinel2 tile list
def get_county_tiles(province, city, county):
    con = psycopg2.connect(
    database="sentinel2",
    user="postgres",
    password="12345",
    host="192.168.2.172",
    port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT "中国有效哨兵格网分布wgs84".mgrs
    FROM china_xy
    JOIN "中国有效哨兵格网分布wgs84" 
    on st_intersects(china_xy.geom,"中国有效哨兵格网分布wgs84".geom)
    WHERE  china_xy.qhmc = '{county}' 
    AND sjxzqdm in (
        SELECT qhdm
        FROM china_dsy
        WHERE qhmc = '{city}'
        AND sjxzqdm in (
            SELECT qhdm
            FROM china_sy
            WHERE china_sy.qhmc = '{province}'
        )  
    )
    """
    cur.execute(sql)
    con.commit()
    tiles=[record[0] for record in cur.fetchall()]
    tiles = [tile.upper() for tile in tiles]
    cur.close()
    con.close()
    return tiles

def ssh_client():
    tiles = input("请输入图幅列表(可选,例如：49slh-50sds): ")
    if tiles == "":
        province = input("请输入省份(必选,例如：河南省): ")
        while province =="":
            province = input("请输入省份(必选,例如：河南省): ")
        city = input("请输入地市(可选,例如：焦作市)：")
        if city == "":
            tiles = get_province_tiles(province)
        else:
            county = input("请输入县名(可选,例如：武陟县)：")
            if county == "":
                tiles = get_city_tiles(province, city)
            else:
                tiles = get_county_tiles(province, city, county)
        tiles = "-".join(tiles)
    print(f"{datetime.datetime.now()}>图幅列表：{tiles}")
    start_date = input("请输入开始时期（例如：2022-03-01）：")
    while start_date == "":
        start_date = input("请输入开始时期（例如：2022-03-01）：")

    end_date = input("请输入截至时期（例如：2022-03-28）：")
    while end_date == "":
        end_date = input("请输入截至时期（例如：2022-03-28）：")
    maxcloud = input("请输入最大含云量（默认100，例如：80）：")
    maxcloud = 100 if maxcloud == "" else int(maxcloud)
    folder = input("请输入83服务器远程目录（必选, 例如：/data）：")
    while folder == "":
        folder = input("请输入82服务器远程目录（必选, 例如：/data）：")
    params = [
        "/home/冯洋洋/PythonEnvironment/RasterShapeEnv/bin/python",
        "/home/冯洋洋/PythonProjection/RasterShape/SynchronizationL1cZip.py",
        f"--tiles {tiles}",
        f"--start_date {start_date}",
        f"--end_date {end_date}",
        f"--maxcloud {maxcloud}",
        f"--folder {folder}"
    ]
    command = " ".join(params)
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy)
    ssh.connect(
        hostname="192.168.2.172",
        port=22, 
        username="root", 
        password="DELL123dell")
    stdin,stdout,stderr = ssh.exec_command(command,get_pty=True)
    for line in iter(stdout.readline,""): 
        print(line)
    ssh.close()
    return

if __name__ == "__main__":
    ssh_client()